﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

using Lenovo.CFI.Common;

namespace Lenovo.CFI.DAL
{
    public class TopicDa
    {
        #region Topic

        /// <summary>
        /// 多条件查询
        /// </summary>
        /// <param name="year">年份</param>
        /// <param name="number">No</param>
        /// <param name="status">状态</param>
        /// <param name="notStatus">不是这个状态</param>
        /// <returns></returns>
        public static List<Topic> GetTopics(int? year, string number, TopicStatus? status, TopicStatus? notStatus)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetTopic", year, number, status, notStatus);

            List<Topic> topics = new List<Topic>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    topics.Add(PopulateTopic(dataReader));
                }
            }

            return topics;
        }

        public static Topic GetTopicByID(int id)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetTopicByID", id);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    return PopulateTopic(dataReader);
                }
            }

            return null;
        }

        private static Topic PopulateTopic(IDataReader dataReader)
        {
            Topic topic = new Topic(dataReader.GetInt32(0));
            topic.Title = dataReader.GetString(1);
            topic.Year = dataReader.GetInt32(2);
            topic.No = dataReader.GetString(3);
            topic.Type = dataReader.GetString(4);
            topic.Tdc = dataReader.GetString(5);
            topic.PptFileID = dataReader.IsDBNull(6) ? (Guid?)null : dataReader.GetGuid(6);
            topic.Status = (TopicStatus)dataReader.GetInt32(7);
            topic.CreateTime = dataReader.GetDateTime(8);
            topic.StartTime = dataReader.IsDBNull(9) ? (DateTime?)null : dataReader.GetDateTime(9);
            topic.DeadlineTime = dataReader.IsDBNull(10) ? (DateTime?)null : dataReader.GetDateTime(10);
            topic.ReviewTime = dataReader.IsDBNull(11) ? (DateTime?)null : dataReader.GetDateTime(11);
            topic.ActionTime = dataReader.IsDBNull(12) ? (DateTime?)null : dataReader.GetDateTime(12);
            topic.ExecuteTime = dataReader.IsDBNull(13) ? (DateTime?)null : dataReader.GetDateTime(13);
            topic.CloseTime = dataReader.IsDBNull(14) ? (DateTime?)null : dataReader.GetDateTime(14);
            topic.SuggestDate = dataReader.IsDBNull(15) ? (DateTime?)null : dataReader.GetDateTime(15);
            topic.ReportDate = dataReader.IsDBNull(16) ? (DateTime?)null : dataReader.GetDateTime(16);
            topic.MaterialDate = dataReader.IsDBNull(17) ? (DateTime?)null : dataReader.GetDateTime(17);
            topic.MktDate = dataReader.IsDBNull(18) ? (DateTime?)null : dataReader.GetDateTime(18);

            return topic;
        }


        public static void InsertTopic(Topic topic)
        {
            InsertTopic(topic, DBHelper.GetDatabase(), null);
        }
        public static void InsertTopic(Topic topic, TranscationHelper trans)
        {
            InsertTopic(topic, trans.DataBase, trans.Transaction);
        }
        private static void InsertTopic(Topic topic, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertTopic",
                topic.Title, topic.Year, topic.No, topic.Type, topic.Tdc, topic.PptFileID,
                (int)topic.Status, topic.CreateTime, topic.StartTime, topic.DeadlineTime,
                topic.ReviewTime, topic.ActionTime, topic.ExecuteTime, topic.CloseTime);

            try
            {
                if (trans == null)
                    topic.ID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
                else
                    topic.ID = Convert.ToInt32(db.ExecuteScalar(dbCommand, trans));
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void UpdateTopic(Topic topic)
        {
            UpdateTopic(topic, DBHelper.GetDatabase(), null);
        }
        public static void UpdateTopic(Topic topic, TranscationHelper trans)
        {
            UpdateTopic(topic, trans.DataBase, trans.Transaction);
        }
        private static void UpdateTopic(Topic topic, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateTopic", topic.ID,
                topic.Title, topic.Year, topic.No, topic.Type, topic.Tdc, topic.PptFileID,
                (int)topic.Status, topic.CreateTime, topic.StartTime, topic.DeadlineTime,
                topic.ReviewTime, topic.ActionTime, topic.ExecuteTime, topic.CloseTime,
                topic.SuggestDate, topic.ReportDate, topic.MaterialDate, topic.MktDate, topic.Principal);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void DeleteTopic(int id)
        {
            DeleteTopic(id, DBHelper.GetDatabase(), null);
        }
        public static void DeleteTopic(int id, TranscationHelper trans)
        {
            DeleteTopic(id, trans.DataBase, trans.Transaction);
        }
        private static void DeleteTopic(int id, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteTopic", id);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, true);
            }
        }

        #endregion

        #region DC and IP

        public static List<TopicDcIp> GetTopicDcIp(int topicID)
        {
            return GetTopicDcIp(topicID, null, null, null);
        }

        public static List<TopicDcIp> GetTopicDcIp(int? topicID, string user, string aspect, bool? aspectType)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetTopicDcIp", topicID, user, aspect, aspectType);

            List<TopicDcIp> topicDcIps = new List<TopicDcIp>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    topicDcIps.Add(PopulateTopicDcIp(dataReader));
                }
            }

            return topicDcIps;
        }

        private static TopicDcIp PopulateTopicDcIp(IDataReader dataReader)
        {
            TopicDcIp topicDcIp = new TopicDcIp(dataReader.GetInt32(0), dataReader.GetInt32(1),
                                dataReader.GetString(2),
                                dataReader.IsDBNull(3) ? null : dataReader.GetString(3));

            return topicDcIp;
        }

        public static void InsertTopicDcIp(TopicDcIp topicDcIp)
        {
            InsertTopicDcIp(topicDcIp, DBHelper.GetDatabase(), null);
        }
        public static void InsertTopicDcIp(TopicDcIp topicDcIp, TranscationHelper trans)
        {
            InsertTopicDcIp(topicDcIp, trans.DataBase, trans.Transaction);
        }
        private static void InsertTopicDcIp(TopicDcIp topicDcIp, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertTopicDcIp", 
                topicDcIp.TopicID, topicDcIp.User, topicDcIp.Aspect);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void DeleteTopicDcIp(int topicID)
        {
            DeleteTopicDcIp(topicID, DBHelper.GetDatabase(), null);
        }
        public static void DeleteTopicDcIp(int topicID, TranscationHelper trans)
        {
            DeleteTopicDcIp(topicID, trans.DataBase, trans.Transaction);
        }
        private static void DeleteTopicDcIp(int topicID, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteTopicDcIp", topicID);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, true);
            }
        }

        #endregion

        #region Reviewer

        public static List<TopicReviewer> GetTopicReviewer(int? topicID, string reviewer, string agent, string reviewerOrAgent)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetTopicReviewer", topicID, reviewer, agent, reviewerOrAgent);

            List<TopicReviewer> reviewers = new List<TopicReviewer>();
            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                while (dataReader.Read())
                {
                    reviewers.Add(PopulateTopicReviewer(dataReader));
                }
            }

            return reviewers;
        }

        public static TopicReviewer GetTopicReviewerByID(int id)
        {
            Database db = DBHelper.GetDatabase();

            DbCommand dbCommand = db.GetStoredProcCommand("Usp_GetTopicReviewerByID", id);

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))
            {
                if (dataReader.Read())
                {
                    return PopulateTopicReviewer(dataReader);
                }
            }

            return null;
        }



        private static TopicReviewer PopulateTopicReviewer(IDataReader dataReader)
        {
            TopicReviewer reviewer = new TopicReviewer(dataReader.GetInt32(0), dataReader.GetInt32(1),
                            dataReader.GetString(2), dataReader.GetString(3));

            return reviewer;
        }



        public static void InsertTopicReviewer(TopicReviewer topicReviewer)
        {
            InsertTopicReviewer(topicReviewer, DBHelper.GetDatabase(), null);
        }
        public static void InsertTopicReviewer(TopicReviewer topicReviewer, TranscationHelper trans)
        {
            InsertTopicReviewer(topicReviewer, trans.DataBase, trans.Transaction);
        }
        private static void InsertTopicReviewer(TopicReviewer topicReviewer, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_InsertTopicReviewer", 
                topicReviewer.TopicID, topicReviewer.Reviewer, topicReviewer.Agent);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void UpdateTopicReviewer(TopicReviewer topicReviewer)
        {
            UpdateTopicReviewer(topicReviewer, DBHelper.GetDatabase(), null);
        }
        public static void UpdateTopicReviewer(TopicReviewer topicReviewer, TranscationHelper trans)
        {
            UpdateTopicReviewer(topicReviewer, trans.DataBase, trans.Transaction);
        }
        private static void UpdateTopicReviewer(TopicReviewer topicReviewer, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_UpdateTopicReviewer", 
                topicReviewer.ID, topicReviewer.Agent);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, false);
            }
        }

        public static void DeleteTopicReviewer(int topicID)
        {
            DeleteTopicReviewer(topicID, DBHelper.GetDatabase(), null);
        }
        public static void DeleteTopicReviewer(int topicID, TranscationHelper trans)
        {
            DeleteTopicReviewer(topicID, trans.DataBase, trans.Transaction);
        }
        private static void DeleteTopicReviewer(int topicID, Database db, DbTransaction trans)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("Usp_DeleteTopicReviewer", topicID);

            try
            {
                if (trans == null)
                    db.ExecuteNonQuery(dbCommand);
                else
                    db.ExecuteNonQuery(dbCommand, trans);
            }
            catch (System.Data.SqlClient.SqlException sex)      // 只捕获SqlException，其余抛出继续传播
            {
                DBHelper.ParseSqlException(sex, true);
            }
        }


        #endregion
    }
}
